package org.example;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.concurrent.ThreadLocalRandom;

public class DatabaseInitializer {
    // 测试数据配置
    private static final int USER_COUNT = 5;      // 生成5个测试用户
    private static final int PRODUCT_COUNT = 10;  // 生成10个测试商品
    private static final int MAX_ORDERS_PER_USER = 5; // 每个用户最多5个订单
    private static final List<String> productNames = Arrays.asList(
            "Apple", "Banana", "Milk", "Bread", "Eggs",
            "Coffee", "Rice", "Chicken", "Fish", "Candy"
    );

    public static void main(String[] args) {
        createDatabaseAndTables();
        Main.main(new String[0]);
    }

    public static void createDatabaseAndTables() {
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = Util.getConn();
            stmt = conn.createStatement();

            // 创建数据库
            stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS supermarket");
            stmt.executeUpdate("USE supermarket");

            // 创建表结构
            createTables(stmt);
            System.out.println("数据库表结构创建完成！");

            // 插入测试数据
            insertTestData(conn);
            System.out.println("测试数据插入完成！");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Util.close(conn, stmt, null);
        }
    }

    private static void createTables(Statement stmt) throws SQLException {
        // 商品表
        stmt.executeUpdate(
                "CREATE TABLE IF NOT EXISTS product (" +
                        "    product_id INT AUTO_INCREMENT PRIMARY KEY, " +
                        "    name VARCHAR(100) NOT NULL, " +
                        "    price DECIMAL(10,2) NOT NULL, " +
                        "    stock INT NOT NULL" +
                        ") ENGINE=InnoDB"
        );

        // 用户表
        stmt.executeUpdate(
                "CREATE TABLE IF NOT EXISTS user (" +
                        "    user_id INT AUTO_INCREMENT PRIMARY KEY, " +
                        "    username VARCHAR(50) UNIQUE NOT NULL, " +
                        "    password VARCHAR(50) NOT NULL, " +
                        "    address VARCHAR(200), " +
                        "    phone VARCHAR(20)" +
                        ") ENGINE=InnoDB"
        );

        // 订单表
        stmt.executeUpdate(
                "CREATE TABLE IF NOT EXISTS `order` (" +
                        "    order_id INT AUTO_INCREMENT PRIMARY KEY, " +
                        "    user_id INT NOT NULL, " +
                        "    product_id INT NOT NULL, " +
                        "    order_time DATETIME NOT NULL, " +
                        "    total_amount DECIMAL(10,2) NOT NULL, " +
                        "    FOREIGN KEY (user_id) REFERENCES user(user_id), " +
                        "    FOREIGN KEY (product_id) REFERENCES product(product_id)" +
                        ") ENGINE=InnoDB"
        );
    }

    private static void insertTestData(Connection conn) throws SQLException {
        // 关闭自动提交以事务方式插入
        conn.setAutoCommit(false);

        try {
            // 插入用户
            List<Integer> userIds = insertUsers(conn);

            // 插入商品
            List<Integer> productIds = insertProducts(conn);

            // 插入订单
            insertOrders(conn, userIds, productIds);

            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }

    // 插入随机用户数据
    private static List<Integer> insertUsers(Connection conn) throws SQLException {
        String sql = "INSERT INTO user (username, password, address, phone) VALUES (?, ?, ?, ?)";
        List<Integer> userIds = new ArrayList<>();

        try (PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            for (int i = 1; i <= USER_COUNT; i++) {
                String username = "user" + i;
                String password = "pass" + i;
                String address = "Address " + i;
                String phone = "138" + String.format("%08d", new Random().nextInt(100000000));

                pstmt.setString(1, username);
                pstmt.setString(2, password);
                pstmt.setString(3, address);
                pstmt.setString(4, phone);
                pstmt.addBatch();

                // 每5条执行一次批量插入
                if (i % 5 == 0) pstmt.executeBatch();
            }
            pstmt.executeBatch();

            // 获取生成的主键
            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                while (rs.next()) {
                    userIds.add(rs.getInt(1));
                }
            }
        }
        return userIds;
    }

    // 插入随机商品数据
    private static List<String> productIds = Arrays.asList(
            "Apple", "Banana", "Milk", "Bread", "Eggs",
            "Coffee", "Rice", "Chicken", "Fish", "Candy"
    );

    private static List<Integer> insertProducts(Connection conn) throws SQLException {
        String sql = "INSERT INTO product (name, price, stock) VALUES (?, ?, ?)";
        List<Integer> productIds = new ArrayList<>();

        try (PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            for (int i = 0; i < PRODUCT_COUNT; i++) {
                String name = productNames.get(i);
                double price = ThreadLocalRandom.current().nextDouble(1, 100);
                int stock = ThreadLocalRandom.current().nextInt(10, 1000);

                pstmt.setString(1, name);
                pstmt.setDouble(2, price);
                pstmt.setInt(3, stock);
                pstmt.addBatch();
            }
            pstmt.executeBatch();

            // 获取生成的主键
            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                while (rs.next()) {
                    productIds.add(rs.getInt(1));
                }
            }
        }
        return productIds;
    }

    // 插入随机订单数据
    private static void insertOrders(Connection conn, List<Integer> userIds, List<Integer> productIds)
            throws SQLException {
        String sql = "INSERT INTO `order` (user_id, product_id, order_time, total_amount) VALUES (?, ?, ?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (Integer userId : userIds) {
                int orderCount = ThreadLocalRandom.current().nextInt(1, MAX_ORDERS_PER_USER + 1);

                for (int i = 0; i < orderCount; i++) {
                    // 随机选择商品
                    int productIndex = ThreadLocalRandom.current().nextInt(productIds.size());
                    int productId = productIds.get(productIndex);

                    // 随机生成订单时间（过去30天内）
                    LocalDateTime orderTime = LocalDateTime.now()
                            .minusDays(ThreadLocalRandom.current().nextInt(30))
                            .minusHours(ThreadLocalRandom.current().nextInt(24));

                    // 随机生成金额
                    double amount = ThreadLocalRandom.current().nextDouble(10, 1000);

                    pstmt.setInt(1, userId);
                    pstmt.setInt(2, productId);
                    pstmt.setTimestamp(3, Timestamp.valueOf(orderTime));
                    pstmt.setDouble(4, amount);
                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
        }
    }
}